package Ms

import (
	"errors"
	"fmt"
	"db2s/global"
	"reflect"
	"runtime"
	"strconv"
	"strings"
	"sync/atomic"
)

type TableSql struct{}

func getAtomicValue(val *atomic.Value) []map[string]string {
	if val == nil{return nil}
	rel := val.Load()
	if rel == nil {
		return nil
	}
	switch fmt.Sprintf("%v", reflect.TypeOf(rel)) {
	case "[]map[string]string":
		return rel.([]map[string]string)
	default:
		return nil
	}
}

/*
columnValueConvert 列数据根据数据类型进行转换
*/
func columnValueConvert(TableColumn []global.TableMeta, cn, s string, logSeq int64) (d string) {
	olds := s
	if strings.Contains(s, "'") {
		s = strings.ReplaceAll(s, "'", "\\'")
	} else {
		s = fmt.Sprintf("'%s'", s)
	}
	if strings.EqualFold(cn, "rowId") {
		return s
	}
	if strings.EqualFold(olds, "NULL") {
		return olds
	}
	for _, i := range TableColumn {
		if strings.EqualFold(i.ColumnName, cn) {
			switch func(s string) (d string) {
				if s == "DATE" {
					d = "DATE"
				}
				if strings.Contains(s, "TIMESTAMP") {
					d = "TIMESTAMP"
				}
				return
			}(strings.ToUpper(i.DataType)) {
			//处理oracle查询时间列时数据带时区问题  2021-01-23 10:16:29 +0800 CST
			case "DATE":
				d = fmt.Sprintf("to_date(%s,'YYYY-MM-DD HH24:MI:SS')", s)
				//处理oracle查询时间列时数据带时区问题  2021-01-23 10:16:29 +0800 CST
			case "TIMESTAMP":
				var du string
				if strings.Contains(i.DataType, "(") && strings.Contains(i.DataType, ")") {
					l := strings.Split(i.DataType, "(")[1]
					du = strings.Split(l, ")")[0]
				}
				if len(du) > 0 {
					d = fmt.Sprintf("to_timestamp(%s,'YYYY-MM-DD HH24:MI:SS.FF%s')", s, du)
				} else {
					d = fmt.Sprintf("to_timestamp(%s,'YYYY-MM-DD HH24:MI:SS')", s)
				}
			default:
				d = s
			}
			break
		}
	}
	return
}
func rowsValueFunc(lobSwitch bool, c, v string) string {
	var tmpColumnName = fmt.Sprintf("'%v'", v)
	switch {
	case strings.EqualFold(v, "<null>"):
		if lobSwitch { //json数据的null值在lob插入是nil
			return fmt.Sprintf("<prepare nil>")
		}
		return fmt.Sprintf("NULL")
	case !strings.HasPrefix(strings.ToUpper(c), "NUMBER") && strings.EqualFold(v, "<entry>"), len(v) == 0:
		if lobSwitch {
			return fmt.Sprintf("<prepare entry>")
		}
		return fmt.Sprintf("''")
	case strings.EqualFold(c, "varbinary"):
		if len(v) == 0 || strings.EqualFold(v, "<nil>") {
			return fmt.Sprintf("NULL")
		}
		return fmt.Sprintf("CAST(%v AS VARBINARY(MAX))", tmpColumnName)
	//case strings.HasPrefix(strings.ToUpper(c), "TIMESTAMP"):
	//	return fmt.Sprintf("TO_TIMESTAMP(%s,'YYYY-MM-DD HH24:MI:SS.FF')", tmpColumnName)
	default:
		return tmpColumnName
	}
}

/*
NullStitching where条件null值查询处理
*/
func NullStitching(s global.WhereGenerateInput) (l string) {
	var (
		event          = "[NullStitching]"
		nullConstraint []string
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v begin dispos where null condition. dispos data is {%v}", s.LogSeq, callFuncInfo, event, s)
	WLog.Debug(vlog)
	if len(s.NullConstraint) > 0 {
		if strings.EqualFold(s.NullConstraint, "null") {
			for _, v := range s.IndexColumn {
				nullConstraint = append(nullConstraint, fmt.Sprintf("%s is null ", v))
			}
			l = strings.Join(nullConstraint, " or ")
		} else if strings.EqualFold(s.NullConstraint, "not null") {
			for _, v := range s.IndexColumn {
				nullConstraint = append(nullConstraint, fmt.Sprintf("%s is not null", v))
			}
			l = strings.Join(nullConstraint, " and ")
		}
	}
	vlog = fmt.Sprintf("(%d) %v %v where null condition processing completed. result is {%v}", s.LogSeq, callFuncInfo, event, nullConstraint)
	WLog.Debug(vlog)
	return
}

/*
LeftStitching 生成sql 语句where left 条件
*/
func LeftStitching(s global.WhereGenerateInput) (l string) {
	var (
		whereLeft []string
	)

	for _, v := range getAtomicValue(s.LeftDirection) {
		columnName, nLeftOk := v["columnName"]
		columnDataLeft, cLeftOk := v["columnDataLeft"]
		querySymbolLeft, qLeftOk := v["querySymbolLeft"]
		if cLeftOk && qLeftOk && nLeftOk {
			if strings.EqualFold(querySymbolLeft, "=") && strings.EqualFold(columnDataLeft, "NULL") { //处理等值null
				whereLeft = append(whereLeft, fmt.Sprintf("\"%s\" %s %s", columnName, " IS ", columnDataLeft))
			} else if strings.EqualFold(querySymbolLeft, ">") && strings.EqualFold(columnDataLeft, "NULL") {
				whereLeft = append(whereLeft, fmt.Sprintf("\"%s\" %s %s", columnName, " IS NOT ", columnDataLeft))
			} else {
				whereLeft = append(whereLeft, fmt.Sprintf("\"%s\" %s %s", columnName, querySymbolLeft, columnValueConvert(s.TableColumn, columnName, columnDataLeft, s.LogSeq)))
			}
		}
	}
	return strings.Join(whereLeft, " and ")
}

/*
RightStitching 生成sql 语句where right 条件
*/
func RightStitching(s global.WhereGenerateInput) string {
	var (
		whereRight []string
	)
	for _, v := range getAtomicValue(s.RightDirection) {
		columnName, nRightOk := v["columnName"]
		columnDataRight, cRightOk := v["columnDataRight"]
		querySymbolRight, qRightOk := v["querySymbolRight"]
		if cRightOk && qRightOk && nRightOk {
			if strings.EqualFold(querySymbolRight, "=") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("\"%s\" %s %s", columnName, " IS ", columnDataRight))
			} else if strings.EqualFold(querySymbolRight, ">") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("\"%s\" %s %s", columnName, " IS NOT ", columnDataRight))
			} else {
				whereRight = append(whereRight, fmt.Sprintf("\"%s\" %s %s", columnName, querySymbolRight, columnValueConvert(s.TableColumn, columnName, columnDataRight, s.LogSeq)))
			}
		}
	}
	return strings.Join(whereRight, " and ")
}
func SqlFilterFunc(s global.WhereGenerateInput) (w string) {
	if len(s.SqlFilter.WhereSql) > 0 {
		return s.SqlFilter.WhereSql
	}
	return
}
func (ms TableSql) OrderBY(s global.WhereGenerateInput) (res string, err error) {
	var orderByColumn []string
	if strings.EqualFold(s.NullConstraint, "null") {
		return
	}
	var uniqOrderByColumn = make(map[string]int)
	for _, v := range s.IndexColumn {
		for _, v1 := range getAtomicValue(s.LeftDirection) {
			if v2, ok := v1["columnName"]; ok {
				if strings.EqualFold(v, v2) {
					if _, ok2 := uniqOrderByColumn[v]; !ok2 {
						uniqOrderByColumn[v]++
						orderByColumn = append(orderByColumn, v)
					}
				}
			}
		}
	}
	res = fmt.Sprintf("%v", strings.Join(orderByColumn, ","))
	return
}

/*
WhereGenerate 生成sql语句的where条件
*/
func (ms TableSql) WhereGenerate(s global.WhereGenerateInput) (ConditionSlice []string, err error) {
	if len(s.SqlFilter.WhereSql) == 0 {
		//if v := NullStitching(s); len(v) > 0 {
		//	ConditionSlice = append(ConditionSlice, v)
		//}
		if v := LeftStitching(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
		if v := RightStitching(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
	} else {
		if v := SqlFilterFunc(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
	}
	return
}
func (ms TableSql) SelectColumn(s global.SelectColumnInput) (columnName, asColumnName []string, err error) {
	var (
		esaColumn      = make(map[string]int)
		allTableColumn []global.TableMeta
	)
	for _, v := range s.Esa.ColumnSlice {
		esaColumn[v]++
	}
	switch s.OutputColumnType {
	case "all":
		allTableColumn = s.TableColumn
	case "specify":
		for _, v1 := range s.SelectColumn {
			for _, vv := range s.TableColumn {
				if strings.EqualFold(vv.ColumnName, v1) {
					allTableColumn = append(allTableColumn, vv)
					break
				}
			}
		}
	default:
		for _, v1 := range s.IndexColumn {
			for _, vv := range s.TableColumn {
				if strings.EqualFold(vv.ColumnName, v1) {
					allTableColumn = append(allTableColumn, vv)
					break
				}
			}
		}
	}
	for _, i := range allTableColumn {
		tmpColumnName := fmt.Sprintf("%s", i.ColumnName)
		asColumn := fmt.Sprintf("C%v", i.ColumnSeq)
		asColumnName = append(asColumnName, asColumn)
		if _, ok := esaColumn[i.ColumnName]; ok && len(s.Esa.UnLockFuncName) > 0 {
			tmpColumnName = fmt.Sprintf("%v.%v(%v) ", s.Schema, s.Esa.UnLockFuncName, tmpColumnName)
		}
		if strings.ToUpper(i.DataType) == "DATE" || strings.ToUpper(i.DataType) == "TIME" || strings.ToUpper(i.DataType) == "DATETIME" {
			//102: YYYY-MM-DD
			tmpColumnName = fmt.Sprintf("CONVERT(VARCHAR(19),%s,120) as %v", tmpColumnName, asColumn)
		} else if strings.HasPrefix(strings.ToUpper(i.DataType), "CHAR") { //解决char 末尾补空的问题
			tmpColumnName = fmt.Sprintf("rtrim(%s) as %v", tmpColumnName, asColumn)
		} else if strings.HasPrefix(strings.ToUpper(i.DataType), "DECIMAL(") || strings.HasPrefix(strings.ToUpper(i.DataType), "NUMERIC(") {
			if v1, ok := s.RowsActiveMapList[strings.ToLower("end0")]; ok {
				if strings.EqualFold(v1, "true") {
					tmpColumnName = fmt.Sprintf("0+CAST(%s as char) as %v", tmpColumnName, asColumn)
				}
			} else {
				tmpColumnName = fmt.Sprintf("%v as %v", tmpColumnName, asColumn)
			}
		} else {
			tmpColumnName = fmt.Sprintf("%v as %v", tmpColumnName, asColumn)
		}
		columnName = append(columnName, tmpColumnName)
	}
	return
}
func (ms TableSql) Prefix(s global.PrefixInput) (insertPrefix string, err error) {
	var (
		tableName string
	)
	if !strings.EqualFold(s.Insert.Prefix.TableInfo.PartitionName, "single") && len(s.Insert.Prefix.TableInfo.PartitionName) > 0 {
		tableName = fmt.Sprintf(" %v.%v PARTITION (%v)", s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table, s.Insert.Prefix.TableInfo.PartitionName)
	} else {
		tableName = fmt.Sprintf(" %v.%v ", s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table)
	}
	insertPrefix = fmt.Sprintf("%s ", fmt.Sprintf("INSERT INTO %v (%s) VALUES", tableName, strings.Join(s.Insert.Prefix.InsertColumn, ",")))
	if s.Insert.Prefix.LobSwitch {
		insertPrefix = fmt.Sprintf("INSERT INTO %v (%s) VALUES (%s)", tableName, strings.Join(s.Insert.Prefix.InsertColumn, ","), strings.Join(s.Insert.Prefix.PlaceholderValue, ","))
	}
	return
}

func (ms TableSql) columnDataDis(p global.PrefixInput, s []*string) []*string {
	var news []*string
	defer func() {
		if err := recover(); err != nil {
			for k, v := range s {
				fmt.Println("--------000000:", k, *v)
			}
			for k, v := range news {
				fmt.Println("--------111111:", k, *v)
			}
		}
	}()
	if p.Insert.Value.IgnoreColumnLength {
		for _, v := range p.Insert.Value.ColData {
			var seq int
			if se, err := strconv.Atoi(v.ColumnSeq); err != nil {
				return nil
			} else {
				seq = se
			}
			if seq > 0 {
				news = append(news, s[seq-1])
			}
		}
	} else {
		news = s
	}
	return news
}

/*
Insert 生成insert语句前缀
*/
func (ms TableSql) Insert(s global.PrefixInput) (any, error) {
	//查询该表的列名和列信息
	var (
		sqlPrefix string
		event     = "[Insert]"
	)
	vlog = fmt.Sprintf("(%d) %v table %s.%s starts to generate insert repair statement.", s.LogSeq, event, s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table)
	WLog.Debug(vlog)
	var manyRowsValue []string
	var lobManyRowsValue [][]*string
	for _, rows := range s.Insert.Value.RowData {
		if len(rows) == 0 {
			continue
		}
		rows = ms.columnDataDis(s, rows)
		var singleRowsValue []string
		var lobSingleRowsValue []*string
		if len(rows) != len(s.Insert.Value.ColData) {
			if s.Insert.Value.IgnoreColumnLength && len(s.Insert.Value.ColData) > len(rows) {
				WLog.Warn(fmt.Sprintf("(%d) %v table %s.%s rows Data length != table column Data. column data is {%v},rows data is {%v}", s.LogSeq, event, s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table, s.Insert.Value.ColData, rows))
			} else {
				WLog.Error(fmt.Sprintf("(%d) %v table %s.%s rows Data length != table column Data. column data is {%v},rows data is {%v}", s.LogSeq, event, s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table, s.Insert.Value.ColData, rows))
				return "", errors.New(fmt.Sprintf("column length abnormal"))
			}
		}
		for k, v := range rows {
			var l = *v
			if strings.Contains(l, "\\") {
				l = strings.ReplaceAll(l, "\\", "\\\\")
			}
			if strings.Contains(l, "'") {
				l = strings.ReplaceAll(l, "'", "\\'")
			}
			if strings.Contains(l, ";") {
				l = strings.ReplaceAll(l, ";", "\\;")
			}
			columnVal := rowsValueFunc(s.Insert.Prefix.LobSwitch, s.Insert.Value.ColData[k].DataType, l)
			if _, ok := s.Insert.Value.Esa.EsaColumnSeq[k]; ok && len(s.Insert.Value.Esa.LockFuncName) > 0 {
				columnVal = fmt.Sprintf("%v.%v(%v)", s.Insert.Prefix.TableInfo.Schema, s.Insert.Value.Esa.LockFuncName, columnVal)
			}
			singleRowsValue = append(singleRowsValue, columnVal)
			lobSingleRowsValue = append(lobSingleRowsValue, &columnVal)
		}
		manyRowsValue = append(manyRowsValue, strings.Join(singleRowsValue, ","))
		lobManyRowsValue = append(lobManyRowsValue, lobSingleRowsValue)
	}
	if strings.Contains(s.Insert.Value.Prefix, "(?") {
		return lobManyRowsValue, nil
	}
	sqlPrefix = fmt.Sprintf("%v (%v);", s.Insert.Value.Prefix, strings.Join(manyRowsValue, "),("))
	vlog = fmt.Sprintf("(%d) %v The insert repair statement of table a is generated. result is {%v}", s.LogSeq, event, sqlPrefix)
	WLog.Debug(vlog)
	return &sqlPrefix, nil
}
func (ms TableSql) Delete(s global.PrefixInput) (any, error) {
	var (
		event          = "[deleteSql]"
		deleteGather   []*string
		deleteSql      string
		whereColumnMap = make(map[int] /*列序号+ 列名字*/ string)
	)
	pc, _, _, _ := runtime.Caller(1)
	callingFunc := runtime.FuncForPC(pc)
	callFile, callLine := callingFunc.FileLine(pc)
	callFunc := callingFunc.Name()
	callFuncInfo := fmt.Sprintf("Call functions %v Call File %v:%v", callFunc, callFile, callLine)
	vlog = fmt.Sprintf("(%d) %v %v table %s.%s starts to generate delete repair statement.", s.LogSeq, callFuncInfo, event, s.Delete.Value.TableInfo.Schema, s.Delete.Value.TableInfo.Table)
	WLog.Debug(vlog)
	for i, v := range s.Delete.Value.ColData {
		switch s.Delete.Value.IndexType {
		case "pri", "uni":
			for m, n := range s.Delete.Value.IndexColumnSeq {
				if strings.EqualFold(fmt.Sprintf("%v", i+1), m) {
					//获取列名和列类型
					whereColumnMap[i] = n
				}
			}
		case "mul":
			//获取列名和列类型
			whereColumnMap[i] = v.ColumnName
		}
	}
	for _, v := range s.Delete.Value.RowData {
		var o []string
		for p, l := range v {
			if c, ok := whereColumnMap[p]; ok {
				q := *l
				if strings.Contains(q, "'") {
					q = strings.ReplaceAll(q, "'", "\\'")
				}
				if strings.Contains(q, "\\") {
					q = strings.ReplaceAll(q, "\\", "\\\\")
				}
				if strings.Contains(q, "'") {
					q = strings.ReplaceAll(q, "'", "\\'")
				}
				if strings.Contains(q, ";") {
					q = strings.ReplaceAll(q, ";", "\\;")
				}
				o = append(o, fmt.Sprintf(" %v = '%v'", c, q))
			}
		}
		if len(o) > 0 {
			deleteSql = fmt.Sprintf("delete from `%s`.`%s` where %s;", s.Delete.Value.TableInfo.Schema, s.Delete.Value.TableInfo.Table, strings.Join(o, " and "))
			WLog.Debug(fmt.Sprintf("(%d) %v %v Generate delete statement successfully, sql is %s", s.LogSeq, callFuncInfo, event, deleteSql))
			deleteGather = append(deleteGather, &deleteSql)
		}
	}
	return deleteGather, nil
}
